﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dropthings.DAL
{
    public class UQ_GetAmmeterTimeDatasInOrNotinWorkTime : BaseQuery
    {
        public UQ_GetAmmeterTimeDatasInOrNotinWorkTime(string identifyCode, int year, int month, int day, bool isInWorkTime)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select isnull(ReadingTime,-1) as ReadingTime, isnull(sum(TimeTotalData),0) TimeTotalData FROM DB_AmmeterTimeDatas");
            sqlStr.AppendFormat(" where readingYear={0}", year);
            sqlStr.AppendFormat(" and ReadingMonth={0}", month);
            sqlStr.AppendFormat(" and ReadingDay={0}", day);
            if (isInWorkTime)
                sqlStr.Append(" and (ReadingTime>=dbo.fun_getWorkStartHour() and ReadingTime<=dbo.fun_getWorkEndHour())");
            else
                sqlStr.Append(" and (ReadingTime<=dbo.fun_getWorkStartHour() or ReadingTime>=dbo.fun_getWorkEndHour())");
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode={0}",identifyCode);
            }
            sqlStr.Append(" group by ReadingTime order by ReadingTime");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterDayDatasInMonth : BaseQuery
    {
        public UQ_GetAmmeterDayDatasInMonth(string identifyCode, int year, int month)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT isnull(ReadingDay,-1) as ReadingDay,ISNULL(b.DayTotalData, 0) AS DayTotalData, a.EnergyItemNo as EnergyItemNo,EnergyItemName");
            sqlStr.Append(" FROM DB_EnergyItemInfo AS a");
            sqlStr.Append(" LEFT OUTER JOIN(SELECT ReadingDay, SUM(ISNULL(DayTotalData, 0)) AS DayTotalData,EnergyItemNo FROM DB_AmmeterDayDatas");
            sqlStr.AppendFormat(" where ReadingYear ={0}", year);
            sqlStr.AppendFormat(" and ReadingMonth ={0}", month);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.AppendFormat(" and EnergyTypeNo ={0}", "01");
            sqlStr.Append(" group by EnergyItemNo,ReadingDay");
            sqlStr.Append(" ) AS b ON a.EnergyItemNo = b.EnergyItemNo ORDER BY ReadingDay,a.EnergyItemNo");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterMonthDatasInYear : BaseQuery
    {
        public UQ_GetAmmeterMonthDatasInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT isnull(ReadingMonth,-1) as ReadingMonth,ISNULL(b.MonthData, 0) AS MonthData, a.EnergyItemNo as EnergyItemNo,EnergyItemName");
            sqlStr.Append(" FROM DB_EnergyItemInfo AS a");
            sqlStr.Append(" LEFT OUTER JOIN(SELECT ReadingMonth, isnull(SUM(ISNULL(MonthData, 0)),0) AS MonthData,EnergyItemNo FROM DB_AmmeterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear ={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.AppendFormat(" and EnergyTypeNo ={0}", "01");
            sqlStr.Append(" group by EnergyItemNo,ReadingMonth");
            sqlStr.Append(" ) AS b ON a.EnergyItemNo = b.EnergyItemNo ORDER BY ReadingMonth,a.EnergyItemNo");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterYearDatasGroupbyProperty : BaseQuery
    {
        public UQ_GetAmmeterYearDatasGroupbyProperty(int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select a.OrgPropertyCode as OrgPropertyCode,OrgPropertyName,b.ReadingYear as ReadingYear,isnull(MonthData,0) MonthData from  DB_OrganizePropertyInfo a ");
            sqlStr.Append(" left join (select ReadingYear,PropertyCode,isnull(sum(isnull(MonthData,0)),0) as MonthData from DB_AmmeterMonthDatas" );
            sqlStr.AppendFormat(" where ReadingYear ={0}", year);
            sqlStr.Append(" group by ReadingYear,PropertyCode) b");
            sqlStr.Append(" on a.OrgPropertyCode=b.PropertyCode order by a.OrgPropertyCode");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterYearDatasByProperty : BaseQuery
    {
        public UQ_GetAmmeterYearDatasByProperty(int year,string property)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select aa.EnergyItemNo EnergyItemNo,EnergyItemName,OrganizeIdentify,OrganizeName,OrganizeProperty,isnull(MonthData,0) MonthData from  DB_EnergyItemInfo aa ");
            sqlStr.Append(" left join(select EnergyItemNo,OrganizeIdentify,OrganizeName,OrganizeProperty,MonthData from DB_OrganizeBaseInfo a ");
            sqlStr.Append(" left join(select ReadingYear,EnergyItemNo,isnull(SUM(isnull(MonthData,0)),0) as MonthData,IdentifyCode from DB_AmmeterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear={0} group by IdentifyCode,ReadingYear,EnergyItemNo) b", year);
            sqlStr.Append(" on a.OrganizeIdentify=b.IdentifyCode");
            sqlStr.AppendFormat(" where OrganizeProperty='{0}'", property);
            sqlStr.Append(" ) bb on aa.EnergyItemNo=bb.EnergyItemNo order by OrganizeIdentify,aa.EnergyItemNo");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterYearDatasGroupbyEnergyItemNo : BaseQuery
    {
        public UQ_GetAmmeterYearDatasGroupbyEnergyItemNo(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT ISNULL(b.MonthData, 0) AS MonthData, a.EnergyItemNo as EnergyItemNo,EnergyItemName FROM DB_EnergyItemInfo AS a ");
            sqlStr.Append(" LEFT OUTER JOIN(SELECT isnull(SUM(ISNULL(MonthData, 0)),0) AS MonthData,EnergyItemNo FROM DB_AmmeterMonthDatas ");
            sqlStr.AppendFormat(" where ReadingYear = {0}", year);
            sqlStr.AppendFormat(" and IdentifyCode ='{0}' and EnergyTypeNo ='01' group by EnergyItemNo) AS b", identifyCode);
            sqlStr.Append(" ON a.EnergyItemNo = b.EnergyItemNo ORDER BY a.EnergyItemNo");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterMonthDataInYearByEnergyItemNo : BaseQuery
    {
        public UQ_GetAmmeterMonthDataInYearByEnergyItemNo(string identifyCode,string energyItemNo,int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT ReadingMonth,isnull(SUM(ISNULL(MonthData, 0)),0) AS MonthData,EnergyItemNo FROM DB_AmmeterMonthDatas ");
            sqlStr.AppendFormat(" where ReadingYear = {0}", year);
            sqlStr.AppendFormat(" and IdentifyCode ='{0}' and EnergyTypeNo ='01' ", identifyCode);
            sqlStr.AppendFormat(" and EnergyItemNo='{0}' ", energyItemNo);
            sqlStr.Append(" group by EnergyItemNo,ReadingMonth");
            _QueryStringName = sqlStr.ToString();
        }
    }
    

    

    public class UQ_GetAmmeterTotalMoneyInYear : BaseQuery
    {
        public UQ_GetAmmeterTotalMoneyInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select isnull(sum(isnull(MonthMoney,0)),0) as Moneys from DB_AmmeterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterMonthMoneyInYear : BaseQuery
    {
        public UQ_GetAmmeterMonthMoneyInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select isnull(ReadingMonth,-1) as ReadingMonth,isnull(sum(isnull(MonthMoney,0)),0) as Moneys from DB_AmmeterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.Append(" group by ReadingMonth order by ReadingMonth");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetAmmeterItemInfo : BaseQuery
    {
        public UQ_GetAmmeterItemInfo()
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select EnergyItemNo, EnergyItemName FROM DB_EnergyItemInfo");
            sqlStr.AppendFormat(" where EnergyTypeNo='{0}'", "01");
            sqlStr.Append(" and IsDelete='false' order by EnergyItemNo");
            _QueryStringName = sqlStr.ToString();
        }
    }
}
